Dates on a spreadsheet imported incorrectly due to Excel date system.
Cause:
The original spreadsheet uses the 1904 date system, which is used to allow an Excel for Windows spreadsheet to be compatible with Excel for Macintosh.
Solution:
Switch to the 1900 date system in the original spreadsheet before importing it.
1) Open the original spreadsheet.
a) In Excel, select the 'File' menu and select 'Open...'. (The Open dialog box appears.)
b) Select the drive that contains the desired spreadsheet from the 'Look in' drop-down list. (A list of all the folders and files within the drive is displayed.)
c) Select the folder that contains the workbook.
NOTE: If the desired spreadsheet is in one of the subfolders, continue selecting folders until the name of the spreadsheet file is displayed.
d) Select the file. (The file is highlighted.)
e) Click 'Open'. (The workbook opens.)
2) Select the 'Tools' menu and select 'Options...'. (The Options dialog box appears.)
3) Click the 'Calculation' tab.
4) Clear the '1904 Date System' check box in the 'Workbook Options' group.
the 1904 Date System check box
5) Click 'OK' to apply the new setting and close the 'Options' dialog box.
6) Import the spreadsheet again.
a) If the destination database (the database to which to import a spreadsheet) is not already open, open the database.
b) If the Database window is not active, activate the Database window.
c) Select the 'File' menu and select 'Get External Data'. (A submenu appears.)
d) Select 'Import...' from the submenu. (The Import dialog box appears.)
e) Select 'Microsoft Excel (*.xls)' from the 'Files of type' drop-down list.
f) Select the drive where the spreadsheet is located from the 'Look in' drop-down list.
g) Select the folder that contains the spreadsheet from the 'Look in' list box. (The spreadsheet file is displayed.)
NOTE: If the file is in a subfolder, continue selecting folders until the desired file is displayed.
h) Select the spreadsheet file. (The file is highlighted.)
i) Click 'Import'. (The Import Spreadsheet Wizard appears.)
Importing an Excel spreadsheet
j) If the spreadsheet has a database password:
1] Type the password in the 'Password' box.
2] Click 'OK'.
k) Do one of the following:
the Import Spreadsheet Wizard
1] Select the 'Show Worksheets' radio button to show all the worksheets in the spreadsheet.
NOTE: Access 7.0 shows multiple worksheets ONLY for Excel 5.0 and 7.0 files. For Excel 4.0 spreadsheet files that contain multiple worksheets, save each worksheet as an individual file before importing.
2] Select the 'Show named ranges' radio button to show all the named ranges in the spreadsheet.
l) Select the worksheet or the range to import.
NOTE: Only one worksheet or range can be imported at a time.
m) Click 'Next'.
n) To import the first row of the worksheet data as field names, select the 'First Row Contains Column Headings' check box.
the First Row Contains Column Headings check box
NOTE: If some data in the first row cannot be used as an Access field name, Access displays a message box. Click 'OK' to have the Wizard automatically assign valid field names for the data.
the Message box
o) Click 'Next'.
p) (Optional) Customize fields in the worksheet:
1] Select a field from the list box at the bottom of the 'Import Spreadsheet Wizard' dialog box. (The entire field or column is highlighted.)
2] Do one or both of the following in the 'Field Options' group:
a] Change the name of the field in the 'Field Name' box.
b] Select one of the following indexing options from the 'Indexed' drop-down list:
1} Select 'No' to not index the field.
2} Select 'Yes (Duplicates OK)' to index the field and allow duplicates.
3} Select 'Yes (No duplicates)' to index the field without duplicates.
3] Select the 'Do not import field (Skip)' check box to not import the selected field.
Customizing fields
q) Click 'Next'.
r) Do one of the following:
1] Select the 'Let Access add Primary Key' radio button to have Access set the primary key for the imported worksheet.
2] Select the 'Choose my own Primary Key' radio button, and select the field to be used as the primary key from the drop-down list next to the radio button.
3] Select the 'No Primary Key' radio button to not set any primary key.
Defining a primary key
s) Click 'Next'.
t) Type the name of the table to which the worksheet is to be imported in the 'Import to Table' box.
the Import to Table box
u) Click 'Finish' to start the import process. (A message appears when the import is complete.)
v) Click 'OK' to close the Import Spreadsheet Wizard.
w) Make sure the data types or other field properties assigned by Access are correct:
